﻿using Model;
using Model.CustomDataType;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DataAccess
{
    public class WorkerDA: DBConnection
    {
        public WorkerDA()
        {
            sqlCommand.Connection = sqlConnection;
        }

        public int addWorker(Worker worker)
        {
            string query = "insert into " + Common.NHAN_VIEN +
                " values(@fullname,@idcard,@sex,@dateofbirth,@address, @phonenumber, @positionid,@username,@password,@status); select SCOPE_IDENTITY();";
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@fullname", worker.getFullName());
            sqlCommand.Parameters.AddWithValue("@idcard", worker.getIdCard());
            sqlCommand.Parameters.AddWithValue("@sex", Utils.convertSexEnumToInt(worker.getSex()));
            CMDateTime myDob = worker.getDateOfBirth();
            DateTime dob = Utils.convertCMDateTimeToDateTime(myDob);
            sqlCommand.Parameters.AddWithValue("@dateofbirth", dob);
            sqlCommand.Parameters.AddWithValue("@address", worker.getAddress());
            sqlCommand.Parameters.AddWithValue("@phonenumber", worker.getPhoneNumber());
            sqlCommand.Parameters.AddWithValue("@positionid", worker.getPositionId());
            sqlCommand.Parameters.AddWithValue("@username", worker.getUsername());
            sqlCommand.Parameters.AddWithValue("@password", worker.getPassword());
            sqlCommand.Parameters.AddWithValue("@status", worker.getStatus());
            try
            {
                open();
                int idRe = -1;
                sqlCommand.CommandText = query;
                string re = sqlCommand.ExecuteScalar().ToString();
                idRe = Int32.Parse(re);
                close();
                return idRe;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                return -1;
            }

        }

        public bool updateWorker(Worker worker)
        {
            //log here
            int patientId = worker.getWorkerId();
            string query = "update " + Common.NHAN_VIEN +
                " set fullname = @fullname, " +
                "idcard = @idcard,sex = @sex,dateofbirth = @dateofbirth,address = @address," +
                "phonenumber = @phonenumber, positionid = @positionid,password = @password,status = @status " +
                "where workerid = " + patientId;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@fullname", worker.getFullName());
            sqlCommand.Parameters.AddWithValue("@idcard", worker.getIdCard());
            sqlCommand.Parameters.AddWithValue("@sex", Utils.convertSexEnumToInt(worker.getSex()));
            CMDateTime myDob = worker.getDateOfBirth();
            DateTime dob = Utils.convertCMDateTimeToDateTime(myDob);
            sqlCommand.Parameters.AddWithValue("@dateofbirth", dob);
            sqlCommand.Parameters.AddWithValue("@address", worker.getAddress());
            sqlCommand.Parameters.AddWithValue("@phonenumber", worker.getPhoneNumber());
            sqlCommand.Parameters.AddWithValue("@positionid", worker.getPositionId());
            sqlCommand.Parameters.AddWithValue("@password", worker.getPassword());
            sqlCommand.Parameters.AddWithValue("@status", worker.getStatus());

            sqlCommand.CommandText = query;

            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (SqlException s)
            {
                //log here
                Console.WriteLine(s.Message);
                return false;
            }
        }

        public Worker getWorkerById(int workerId)
        {
            string query = "select * from " + Common.NHAN_VIEN + " where workerid=" + workerId;
            sqlCommand.CommandText = query;
            try
            {

                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                Worker worker = new Worker();
                while (sqlDataReader.Read())
                {
                    worker = createWorkerFromReader(sqlDataReader);

                }
                close();
                return worker;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }


        }

        public Worker[] getAllWorker()
        {
            string query = "select * from " + Common.NHAN_VIEN;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<Worker> liWorker = new List<Worker>();
                Worker worker = new Worker();
                while (sqlDataReader.Read())
                {
                    worker = createWorkerFromReader(sqlDataReader);
                    liWorker.Add(worker);
                }
                close();
                return liWorker.ToArray();
            }
            catch(Exception e)
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }


        }

        public bool updatePassword(int workerId, string newPass)
        {
            string query = "update " + Common.NHAN_VIEN +
                " set password = @password, " +
                "where workerid = " + workerId;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@password", newPass);
            sqlCommand.CommandText = query;

            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (SqlException s)
            {
                //log here
                Console.WriteLine(s.Message);
                return false;
            }
        }
        
        public Worker[] getWorkersByRawQuery(string query)
        {
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<Worker> liPatient = new List<Worker>();
                Worker worker = null;
                while (sqlDataReader.Read())
                {
                    worker = new Worker();
                    string str = "";
                    for (int i = 0; i < sqlDataReader.FieldCount; i++)
                    {
                        str = sqlDataReader.GetName(i);//Get name of column;

                        str.ToLower();
                        switch (str)
                        {
                            case "workerid":
                                worker.setWorkerId(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "fullname":
                                worker.setFullName(sqlDataReader.GetValue(i).ToString());
                                break;
                            case "idcard":
                                worker.setIdCard(sqlDataReader.GetValue(i).ToString());
                                break;
                            case "sex":
                                worker.setSex(Utils.convertIntToSexEnum(
                                    Int16.Parse(sqlDataReader.GetValue(i).ToString())));
                                break;
                            case "dateofbirth":
                                worker.setDateOfBirth(Utils.convertDateTimeToCMDateTime((DateTime)sqlDataReader.GetValue(i)));
                                break;
                            case "address":
                                worker.setAddress(sqlDataReader.GetValue(i).ToString());
                                break;
                            case "phonenumber":
                                worker.setPhoneNumber(sqlDataReader.GetValue(i).ToString());
                                break;
                            case "positionid":
                                worker.setPositionId(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "username":
                                worker.setUsername(sqlDataReader.GetValue(i).ToString());
                                break;
                            case "password":
                                worker.setPassword(sqlDataReader.GetValue(i).ToString());
                                break;
                            case "status":
                                worker.setStatus(Int16.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                        }
                    }
                    liPatient.Add(worker);
                }
                close();
                return liPatient.ToArray();
            }
            catch
            {
                return null;
            }
            finally
            {
                if(sqlDataReader!=null)
                    sqlDataReader.Close();
                close();
            }
        }

        private Worker createWorkerFromReader(SqlDataReader rd)
        {

            if (rd != null && !rd.IsClosed)
            {
                Worker worker = new Worker();
                worker.setWorkerId(rd.GetInt32(0));
                worker.setFullName(rd.GetString(1));
                worker.setIdCard(rd.GetString(2));
                worker.setSex(Utils.convertIntToSexEnum(rd.GetInt32(3)));
                DateTime dt = rd.GetDateTime(4);
                worker.setDateOfBirth(Utils.convertDateTimeToCMDateTime(dt));
                worker.setAddress(rd.GetString(5));
                worker.setPhoneNumber(rd.GetString(6));
                worker.setPositionId(rd.GetInt32(7));
                worker.setUsername(rd.GetString(8));
                worker.setPassword(rd.GetString(9));
                worker.setStatus(rd.GetInt16(10));
                return worker;
            }
            else
            {
                return null;
            }

        }
    }
}
